/*
 * This file is part of ELCube.
 *
 * ELCube is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * ELCube is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with ELCube.  If not, see <https://www.gnu.org/licenses/>.
 */
package cn.nkpro.elcube.bigdata.controller;

import cn.nkpro.elcube.annotation.NkNote;
import cn.nkpro.elcube.co.query.model.DataFieldDesc;
import cn.nkpro.elcube.co.query.model.DataQueryRequest;
import cn.nkpro.elcube.co.query.model.DataQueryResponse;
import cn.nkpro.elcube.co.query.DataSourceManager;
import cn.nkpro.elcube.co.query.model.DataSourceDef;
import cn.nkpro.elcube.data.redis.RedisSupport;
import cn.nkpro.elcube.exception.NkDefineException;
import cn.nkpro.elcube.utils.DateTimeUtilz;
import cn.nkpro.elcube.utils.UUIDHexGenerator;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.*;

import java.io.ByteArrayOutputStream;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * Created by bean on 2020/6/9.
 */
@Slf4j
@NkNote("3.数据分析与发现")
@RestController
@RequestMapping("/data/analyse")
public class DataAnalyseController {

    private final static TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
    @Autowired@SuppressWarnings("all")
    private RedisSupport<ExportCache> redis;

    @SuppressWarnings("all")
    @Autowired
    private DataSourceManager dataSourceManager;


    @PreAuthorize("authenticated")
    @NkNote("1.数据源列表")
    @RequestMapping(value = "/dataSources",method = RequestMethod.GET)
    public Collection<DataSourceDef> getDataSources(){
        return dataSourceManager.getDataSources();
    }

    @PreAuthorize("authenticated")
    @NkNote("1.索引字段列表")
    @RequestMapping(value = "/fieldCaps/{datasourceName}",method = RequestMethod.POST)
    public List<DataFieldDesc> getFieldCaps(@PathVariable String datasourceName) {
        return dataSourceManager.getService(datasourceName).getFieldCaps(datasourceName);
    }

    @PreAuthorize("authenticated")
    @NkNote("2.执行sql")
    @RequestMapping(value = "/query",method = RequestMethod.POST)
    public DataQueryResponse sql(@RequestBody DataQueryRequest request) {


        Select select = request.parse().getSelects().stream().findFirst().orElse(null);
        if(select==null){
            throw new NkDefineException("查询命令不能为空");
        }
        String tableName = tablesNamesFinder.getTableList(select).stream().findFirst().orElse(null);
        if(tableName==null){
            throw new NkDefineException("数据源没有指定");
        }

        return dataSourceManager.getService(tableName).queryPage(request);
    }


    @NoArgsConstructor
    @AllArgsConstructor
    @Data
    private static class ExportCache{
        private List<DataQueryRequest.DataColumn> columns;
        private List<Map<String, Object>> list;

    }

    @PreAuthorize("authenticated")
    @NkNote("3.导出sql结果")
    @RequestMapping(value = "/export",method = RequestMethod.POST)
    public String export(@RequestBody DataQueryRequest request) {

        request.setFrom(0);
        request.setRows(10000);
        request.setCursor(null);

        Select select = request.parse().getSelects().stream().findFirst().orElse(null);
        if(select==null){
            throw new NkDefineException("查询命令不能为空");
        }
        String tableName = tablesNamesFinder.getTableList(select).stream().findFirst().orElse(null);
        if(tableName==null){
            throw new NkDefineException("数据源没有指定");
        }

        DataQueryResponse<Map<String,Object>> dataQueryResponse = dataSourceManager.getService(tableName).queryPage(request);
        List<Map<String, Object>> list = dataQueryResponse.getList();

        String key = UUIDHexGenerator.generate();
        redis.set(key, new ExportCache(request.getColumns(),list));
        redis.expire(key,60);
        return key;
    }

    private void createHead(XSSFSheet sheet, XSSFCellStyle  cellStyleHeader, List<DataQueryRequest.DataColumn> columns, AtomicInteger x, AtomicInteger y,DataQueryRequest.DataColumn column){
        XSSFRow head = sheet.getRow(y.get())==null?sheet.createRow(y.get()):sheet.getRow(y.get());
        columns.forEach(c->{

            XSSFCell cell = head.createCell(x.get());
            cell.setCellStyle(cellStyleHeader);
            cell.setCellValue(c.getTitle());

            c.setRow(head.getRowNum());
            c.setMax_row(head.getRowNum());
            c.setCol(x.get());
            c.setMax_col(x.get());

            if(c.getChildren()!=null&&!c.getChildren().isEmpty()){
                y.addAndGet(1);
                createHead(sheet, cellStyleHeader, c.getChildren(),x,y,c);
            }else{
                x.addAndGet(1);
            }

            if(column!=null){
                column.setMax_col(Math.max(column.getMax_col(),x.get()-1));
            }
        });
    }

    private void mergedRegion(XSSFSheet sheet, List<DataQueryRequest.DataColumn> columns, int maxY,List<DataQueryRequest.DataColumn> dataColumns){
        columns.forEach(c->{
            if(c.getChildren()!=null&&!c.getChildren().isEmpty()){
                mergedRegion(sheet,c.getChildren(),maxY,dataColumns);
            }else{
                c.setMax_row(Math.max(c.getMax_row(),maxY));
                dataColumns.add(c);
            }
            if(c.getCol()!=c.getMax_col()||c.getRow()!=c.getMax_row()){
                CellRangeAddress region = new CellRangeAddress(c.getRow(),c.getMax_row(),c.getCol(),c.getMax_col());
                sheet.addMergedRegion(region);
            }
        });
    }

    @NkNote("3.生成下载URL")
    @RequestMapping(value = "/download/{id}/{name}",method = RequestMethod.GET)
    public ResponseEntity<?> download(@PathVariable String id, @PathVariable String name) {

        ExportCache cache = redis.get(id);

        redis.delete(id);
        if(cache==null){
            return null;
        }

        XSSFWorkbook workbook = new XSSFWorkbook();

        // 创建央视
        XSSFFont fontHeader = workbook.createFont();
        fontHeader.setBold(true);
        XSSFDataFormat format = workbook.createDataFormat();

        XSSFCellStyle cellStyleHeader = workbook.createCellStyle();
        cellStyleHeader.setFont(fontHeader);
        //cellStyleHeader.setAlignment(HorizontalAlignment.CENTER);
        cellStyleHeader.setVerticalAlignment(VerticalAlignment.CENTER);

        XSSFCellStyle cellStyleDate = workbook.createCellStyle();
        cellStyleDate.setDataFormat(format.getFormat("yyyy/m/d"));

        XSSFCellStyle cellStyleDateTime = workbook.createCellStyle();
        cellStyleDateTime.setDataFormat(format.getFormat("yyyy/m/d h:mm:ss"));

        XSSFCellStyle cellStyleCurrency = workbook.createCellStyle();
        cellStyleCurrency.setDataFormat(format.getFormat("¥#,##0.00"));

        XSSFCellStyle cellStylePercent = workbook.createCellStyle();
        cellStylePercent.setDataFormat(format.getFormat("0.0000%"));

        XSSFCellStyle cellStyleNumber = workbook.createCellStyle();
        cellStyleNumber.setDataFormat(format.getFormat("#0.00"));

        XSSFCellStyle cellStyleInteger = workbook.createCellStyle();
        cellStyleInteger.setDataFormat(format.getFormat("#0"));

        // 创建Sheet
        XSSFSheet sheet = workbook.createSheet("Sheet");



        // 设置表头，并合并单元格
        List<DataQueryRequest.DataColumn> columns = new ArrayList<>();
        AtomicInteger x = new AtomicInteger(0);
        AtomicInteger y = new AtomicInteger(0);
        createHead(sheet,cellStyleHeader,cache.getColumns(),x,y,null);
        mergedRegion(sheet,cache.getColumns(),y.get(),columns);


        for(int i=0;i<cache.getList().size();i++){
            Map<String, Object> map = cache.getList().get(i);
            XSSFRow row = sheet.createRow(i+y.get()+1);

            columns.stream()
                    .filter(column -> StringUtils.isNotBlank(column.getField()))
                    .forEach(column->{

                        String field = column.getField();
                        Object value = map.get(field);

                        int index = columns.indexOf(column);

                        if(value!=null){
                            if(value instanceof Number){
                                XSSFCell cell = row.createCell(index, CellType.NUMERIC);
                                if(column.getFormatter()!=null){

                                    CharSequence f = null;
                                    if(column.getFormatter() instanceof List){
                                        List formatter = (List) column.getFormatter();
                                        if(!formatter.isEmpty()){
                                            f = (String) formatter.get(0);
                                        }
                                    }else if(column.getFormatter() instanceof String){
                                        f = (String) column.getFormatter();
                                    }

                                    if(StringUtils.equalsAnyIgnoreCase(f,"nkDatetime")){
                                        cell.setCellValue(new Date(((Number) value).longValue()*1000));
                                        cell.setCellStyle(cellStyleDateTime);
                                        return;
                                    }else if(StringUtils.equalsAnyIgnoreCase(f,"nkDate")){
                                        cell.setCellValue(new Date(((Number) value).longValue()*1000));
                                        cell.setCellStyle(cellStyleDate);
                                        return;
                                    }else if(StringUtils.equalsAnyIgnoreCase(f,"nkDatetimeFriendly")){
                                        cell.setCellValue(new Date(((Number) value).longValue()*1000));
                                        cell.setCellStyle(cellStyleDateTime);
                                        return;
                                    }else if(StringUtils.equalsAnyIgnoreCase(f,"nkDatetimeISO")){
                                        cell.setCellValue(new Date(((Number) value).longValue()));
                                        cell.setCellStyle(cellStyleDateTime);
                                        return;
                                    }else if(StringUtils.equalsAnyIgnoreCase(f,"nkDateISO")){
                                        cell.setCellValue(new Date(((Number) value).longValue()));
                                        cell.setCellStyle(cellStyleDate);
                                        return;
                                    }else if(StringUtils.equalsAnyIgnoreCase(f,"nkMillisecondFriendly")){
                                        cell.setCellValue(new Date(((Number) value).longValue()));
                                        cell.setCellStyle(cellStyleDateTime);
                                        return;
                                    }else if(StringUtils.equalsAnyIgnoreCase(f,"nkCurrency")){
                                        cell.setCellValue(((Number) value).doubleValue());
                                        cell.setCellStyle(cellStyleCurrency);
                                        return;
                                    }else if(StringUtils.equalsAnyIgnoreCase(f,"nkPercent")){
                                        cell.setCellValue(((Number) value).doubleValue());
                                        cell.setCellStyle(cellStylePercent);
                                        return;
                                    }else if(StringUtils.equalsAnyIgnoreCase(f,"nkCurrency")){
                                        cell.setCellValue(((Number) value).doubleValue());
                                        cell.setCellStyle(cellStyleCurrency);
                                        return;
                                    }else if(StringUtils.equalsAnyIgnoreCase(f,"nkNumber","nkDouble","nkFloat")){
                                        cell.setCellValue(((Number) value).doubleValue());
                                        cell.setCellStyle(cellStyleNumber);
                                        return;
                                    }else if(StringUtils.equalsAnyIgnoreCase(f,"nkInteger","nkInt")){
                                        cell.setCellValue(((Number) value).doubleValue());
                                        cell.setCellStyle(cellStyleInteger);
                                        return;
                                    }
                                }

                                cell.setCellValue(((Number)value).doubleValue());
                            }else{
                                if(column.getFormatter()!=null){
                                    CharSequence f = null;
                                    if(column.getFormatter() instanceof List){
                                        List formatter = (List) column.getFormatter();
                                        if(!formatter.isEmpty()){
                                            f = (String) formatter.get(0);
                                        }
                                    }else if(column.getFormatter() instanceof String){
                                        f = (String) column.getFormatter();
                                    }
                                    if(StringUtils.equalsAnyIgnoreCase(f,"nkDatetimeISO")){
                                        XSSFCell cell = row.createCell(index, CellType.NUMERIC);
                                        cell.setCellValue(new Date(1000*DateTimeUtilz.fromZonedISO((String) value)));
                                        cell.setCellStyle(cellStyleDate);
                                        return;
                                    }
                                }
                                row.createCell(index).setCellValue(value.toString());
                            }
                        }
                    });
        }

        for(int i=0;i<columns.size();i++)
            sheet.autoSizeColumn(i);

        ByteArrayOutputStream fileOut;
        try {
            fileOut = new ByteArrayOutputStream();
            workbook.write(fileOut);
            fileOut.close();

            byte[] bytes = fileOut.toByteArray();

            HttpHeaders headers = new HttpHeaders();
            headers.setContentDispositionFormData("attachment",
                    new String((name+".xlsx").getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
            headers.setContentLength(bytes.length);
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);

            return ResponseEntity.ok()
                    .headers(headers)
                    .contentLength(bytes.length)
                    .contentType(MediaType.parseMediaType("application/octet-stream"))
                    .body(new ByteArrayResource(bytes));

        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return ResponseEntity.noContent().build();
        }
    }
}
